package com.pineapple.module.core.standards.fertilizer.service;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.pineapple.module.core.standards.fertilizer.domain.Fertilizer;
import com.pineapple.module.core.standards.fertilizer.domain.FertilizerCriteria;
import com.pineapple.module.core.standards.fertilizer.domain.FertilizerSearch;



public class FertilizerDAO {

	protected List<FertilizerSearch> search(FertilizerCriteria criteria,Connection conn) throws Exception{
		List<FertilizerSearch> lstFertilizer = new ArrayList<FertilizerSearch>();
		FertilizerSearch fertilizer = null;
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT * FROM fertilizer ");
		sql.append("WHERE 1=1 ");

		if(criteria.getFertilizerId()!=null && !criteria.getFertilizerId().equals("")){
			sql.append("AND fertilizer_id  LIKE '"+criteria.getFertilizerId()+"%' ");
		}
		if(criteria.getFertilizerName()!=null && !criteria.getFertilizerName().equals("")){
			sql.append("AND fertilizer_name LIKE '"+criteria.getFertilizerName()+"%' ");
		}
		if(criteria.getFertilizerType()!=null && !criteria.getFertilizerType().equals("")){
			sql.append("AND fertilizer_type LIKE '"+criteria.getFertilizerType()+"%' ");
		}


		try(Statement stmt = conn.createStatement()){
			  ResultSet rs = stmt.executeQuery(sql.toString());
		        while(rs.next()) {
		        	fertilizer= new FertilizerSearch();
		        	fertilizer.setFertilizerId(rs.getString("fertilizer_id"));
		        	fertilizer.setFertilizerName(rs.getString("fertilizer_name"));
		        	fertilizer.setFertilizerType(rs.getString("fertilizer_type"));
		        	fertilizer.setFertilizerFormula(rs.getString("fertilizer_formula"));

		        	lstFertilizer.add(fertilizer);
		        }


		}catch(Exception ex){
			ex.printStackTrace();
			throw ex;
		}
		return lstFertilizer;
	}



	protected void add(Connection conn, Fertilizer fertilizer)throws Exception{

		StringBuilder sql = new StringBuilder();
		sql.append("INSERT INTO fertilizer (fertilizer_name,fertilizer_formula,fertilizer_type)");
		sql.append("VALUES (");

		sql.append("'"+fertilizer.getFertilizerName()+"' ");
		sql.append(",'"+fertilizer.getFertilizerFormula()+"' ");
		sql.append(",'"+fertilizer.getFertilizerType()+"' ");
		sql.append(" )");

			System.out.println(sql.toString());
		try(Statement stmt = conn.createStatement()){
			 stmt.executeUpdate(sql.toString());

		}catch(Exception ex){
			ex.printStackTrace();
			throw ex;
		}
	}


	protected void edit(Connection conn, Fertilizer fertilizer)throws Exception{

		StringBuilder sql = new StringBuilder();
		sql.append("UPDATE fertilizer SET ");
		sql.append("fertilizer_name='"+fertilizer.getFertilizerName()+"' ");
		sql.append(",fertilizer_formula='"+fertilizer.getFertilizerFormula()+"' ");
		sql.append(",fertilizer_type='"+fertilizer.getFertilizerType()+"' ");
		sql.append(" WHERE fertilizer_id="+fertilizer.getFertilizerId());


		System.out.println(sql.toString());
		try(Statement stmt = conn.createStatement()){
			stmt.executeUpdate(sql.toString());

		}catch(Exception ex){
			ex.printStackTrace();
			throw ex;
		}
	}

	protected void delete(Connection conn, String fertilizerIds)throws Exception{

		StringBuilder sql = new StringBuilder();
		sql.append("DELETE FROM fertilizer  ");

		sql.append(" WHERE fertilizer_id IN ("+fertilizerIds+")");
		System.out.println(sql.toString());
		try(Statement stmt = conn.createStatement()){
			stmt.executeUpdate(sql.toString());
		}catch(Exception ex){
			ex.printStackTrace();
			throw ex;
		}
	}


	protected Fertilizer searchById(Connection conn,int id)throws Exception{

		StringBuilder sql = new StringBuilder();
		sql.append("SELECT * FROM fertilizer ");
		sql.append("WHERE fertilizer_id ="+id);
		Fertilizer fertilizer = null;
		try(Statement stmt = conn.createStatement()){
			  ResultSet rs = stmt.executeQuery(sql.toString());
			   fertilizer = new Fertilizer();
		        if(rs.next()) {
		        	fertilizer.setFertilizerId(rs.getString("fertilizer_id"));
		        	fertilizer.setFertilizerName(rs.getString("fertilizer_name"));
		        	fertilizer.setFertilizerFormula(rs.getString("fertilizer_formula"));
		        	fertilizer.setFertilizerType(rs.getString("fertilizer_type"));

		        }

		}catch(Exception ex){
			ex.printStackTrace();
			throw ex;
		}
		return fertilizer;
	}
}
